package org.gntc.prayer.db;

import org.gntc.prayer.common.PrayerInfo;
import org.gntc.prayer.db.PrayerDBInfo.AnswerTBL;
import org.gntc.prayer.db.PrayerDBInfo.CategoryTBL;
import org.gntc.prayer.db.PrayerDBInfo.PrayerTBL;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.util.Log;

public class PrayerDBMngr extends DBConnection
{
	/**
	 * 생성자
	 * @param context
	 */
	public PrayerDBMngr(Context context)
	{
		super(context);
	}
	
	/**
	 * 카테고리 저장
	 * @param category_name
	 * @return
	 * @throws SQLiteException
	 */
	public long insertCategory(String category_name) throws SQLiteException
	{
		ContentValues values = new ContentValues();
		values.put(CategoryTBL.COL_NAME, category_name);
		
		SQLiteDatabase db = this.getM_DB();
		
		return db.insertOrThrow(PrayerDBInfo.CATEGORY_TABLE_NAME, null, values);
	}
	
	/**
	 * Prayer 저장
	 * @param category
	 * @param content
	 * @param setAlarm
	 * @param alarmDate
	 * @return
	 * @throws SQLiteException
	 */
	public long insertPrayer(int nCategoryID, String strContent, boolean bSetAlarm, String strAlarmTime) throws SQLiteException
	{
		ContentValues values = new ContentValues();
		
		values.put(PrayerTBL.CATEGORY_ID, nCategoryID);			// 카테고리 ID
		values.put(PrayerTBL.PRAYER, strContent);					// 기도 제목
		values.put(PrayerTBL.SET_ALARM, bSetAlarm);				// 알람 설정 여부
		values.put(PrayerTBL.ALARM_TIME, strAlarmTime);			// 알람 시간
		
		SQLiteDatabase db = this.getM_DB();
		
		return db.insertOrThrow(PrayerDBInfo.PRAYER_TABLE_NAME, null, values);
	}
	
	/**
	 * 카테고리 이름 구하기
	 * @return
	 */
	public Cursor getAllCategoryName()
	{
		SQLiteDatabase db = this.getM_DB();
		Cursor cursor = db.query(PrayerDBInfo.CATEGORY_TABLE_NAME, null, null, null, null, null, CategoryTBL._ID);

		if (cursor != null && cursor.getCount() != 0)
		{
			cursor.moveToFirst();
		}
		
		return cursor;
	}
	
	/**
	 * 같은 이름의 Category 이름이 있는지 검사
	 * @param strName
	 * @return
	 */
	public int checkCategoryName(String strName)
	{
		int nVal = -1;
		String strQuery = "SELECT _id FROM " + PrayerDBInfo.CATEGORY_TABLE_NAME + " WHERE " + CategoryTBL.COL_NAME + " = ? ";
		String strParams[] = {strName};
		
		SQLiteDatabase db = this.getM_DB();
		Cursor c = db.rawQuery(strQuery, strParams);
		c.moveToFirst();
		
		if (c.getCount() > 0)
		{
			nVal = c.getInt(0);
		}
		
		c.close();
		
		return nVal;
	}
	
	/**
	 * Prayer 구하기
	 * @return
	 */
	public Cursor getAllPrayerList()
	{
		String strQuery = "";
		
		strQuery += " SELECT ";
		strQuery +=		" p._id,";
		strQuery +=		" p.categoryid,";
		strQuery +=		" p.prayer,";
		strQuery +=		" p.setalarm,";
		strQuery +=		" c.catename, ";
		strQuery +=		" p.regdate ";
		strQuery += " FROM ";
		strQuery +=		" prayer_tbl p ";
		strQuery +=		" INNER JOIN ";
		strQuery +=		" prayer_category_tbl c";
		strQuery +=		" ON ";
		strQuery +=		" p.categoryid = c._id ";
		strQuery += " ORDER BY ";
		strQuery += 		" c.catename ASC, ";
		strQuery += 		" p._id DESC ";
		
		SQLiteDatabase db = this.getM_DB();
		
		return db.rawQuery(strQuery, null);
	}
	
	/**
	 * Prayer 정보 구하기
	 * @param nPrayerID
	 * @return
	 */
	public PrayerInfo getPrayerInfo(int nPrayerID)
	{
		PrayerInfo pi = null;
		
		// Query
		String strQuery = "";
		
		strQuery += "SELECT ";
		strQuery +=		"p.categoryid, ";
		strQuery +=		"p.prayer, ";
		strQuery +=		"p.regdate, ";
		strQuery +=		"p.setalarm, ";
		strQuery +=		"p.alarmtime, ";
		strQuery +=		"c.catename ";
		strQuery += "FROM ";
		strQuery +=		PrayerDBInfo.PRAYER_TABLE_NAME + " p ";
		strQuery +=		"INNER JOIN ";
		strQuery +=		PrayerDBInfo.CATEGORY_TABLE_NAME + " c ";
		strQuery +=		"ON p.categoryid = c._id ";
		strQuery += "WHERE p._id=" + nPrayerID;
		
		SQLiteDatabase db = this.getM_DB();
		Cursor c = db.rawQuery(strQuery, null);
		c.moveToFirst();
		
		if (c.getCount() > 0)
		{
			pi = new PrayerInfo();
			
			pi.setM_nCategoryID(c.getInt(0));				// Category ID
			pi.setM_strCateName(c.getString(5));			// Category name
			pi.setM_strPrayer(c.getString(1));				// Prayer
			pi.setM_strRegDate(c.getString(2));			// Registration date
			
			boolean bIsSetAlarm = (c.getInt(3) == 0 ? false : true);
			pi.setM_bIsSetAlarm(bIsSetAlarm);			// Set Alarm
			
			pi.setM_strAlarmTime(c.getString(4));			// Alarm time
		}
		
		c.close();
		
		return pi;
	}
	
	/**
	 * Prayer 삭제
	 * @param nPrayerID
	 */
	public void deletePrayer(int nPrayerID)
	{
		// Query
		String strQuery = "DELETE FROM " + PrayerDBInfo.PRAYER_TABLE_NAME + " WHERE " + PrayerTBL._ID + "=" + nPrayerID;
		SQLiteDatabase db = this.getM_DB();
		db.execSQL(strQuery);
	}
	
	/**
	 * Prayer 수정
	 * @param nPrayerID
	 * @param nCategoryID
	 * @param strContent
	 * @param bSetAlarm
	 * @param strAlarmTime
	 * @return
	 * @throws SQLiteException
	 */
	public long updatePrayer(int nPrayerID, int nCategoryID, String strPrayer, boolean bSetAlarm, String strAlarmTime) throws SQLiteException
	{
		ContentValues values = new ContentValues();
		
		values.put(PrayerTBL.CATEGORY_ID, nCategoryID);			// 카테고리 ID
		values.put(PrayerTBL.PRAYER, strPrayer);						// Prayer
		values.put(PrayerTBL.SET_ALARM, bSetAlarm);				// 알람 설정 여부
		values.put(PrayerTBL.ALARM_TIME, strAlarmTime);			// 알람 시간
		
		String strWhere = PrayerTBL._ID + "=" + nPrayerID;
		
		SQLiteDatabase db = this.getM_DB();
		
		return db.update(PrayerDBInfo.PRAYER_TABLE_NAME, values, strWhere, null);
	}
	
	/**
	 * Answer 저장
	 * @param nPrayerID
	 * @param strAnswer
	 * @return
	 */
	public long insertAnswer(int nPrayerID, String strAnswer)
	{
		ContentValues values = new ContentValues();
		
		values.put(AnswerTBL.PRAYER_ID, nPrayerID);				// Prayer ID
		values.put(AnswerTBL.ANSWER, strAnswer);				// Answer
		
		SQLiteDatabase db = this.getM_DB();
		
		return db.insertOrThrow(PrayerDBInfo.ANSWER_TABLE_NAME, null, values);
	}
	
	/**
	 * Get answer list
	 * @param nPrayerID
	 * @return
	 */
	public Cursor getAllAnswerList(int nPrayerID)
	{
		String strQuery = "";
		
		strQuery += " SELECT ";
		strQuery +=		" _id,";
		strQuery +=		" answer,";
		strQuery +=		" regdate ";
		strQuery += " FROM ";
		strQuery +=		" answer_tbl ";
		strQuery += " WHERE ";
		strQuery +=		" prayerid=" + nPrayerID + " ";
		strQuery += " ORDER BY ";
		strQuery += 		" regdate ASC ";
		
		SQLiteDatabase db = this.getM_DB();
		
		return db.rawQuery(strQuery, null);
	}
	
	/**
	 * Get answer
	 * @param nAnswerID
	 * @return
	 */
	public String selectAnswerInfo(int nAnswerID)
	{
		String strAnswer = "";
		
		// Query
		String strQuery = "";
		
		strQuery += "SELECT ";
		strQuery +=		"answer ";
		strQuery += "FROM ";
		strQuery +=		PrayerDBInfo.ANSWER_TABLE_NAME + " ";
		strQuery += "WHERE ";
		strQuery += 		"_id=" + nAnswerID;
		
		SQLiteDatabase db = this.getM_DB();
		Cursor c = db.rawQuery(strQuery, null);
		c.moveToFirst();
		
		if (c.getCount() > 0)
		{
			strAnswer = c.getString(0);
		}
		
		c.close();
		
		return strAnswer;
	}
	
	/**
	 * Answer 삭제
	 * @param nPrayerID
	 */
	public void deleteAnswer(int nAnswerID)
	{
		// Query
		String strQuery = "DELETE FROM " + PrayerDBInfo.ANSWER_TABLE_NAME + " WHERE " + AnswerTBL._ID + "=" + nAnswerID;
		SQLiteDatabase db = this.getM_DB();
		db.execSQL(strQuery);
	}
	
	/**
	 * Answer 수정
	 * @param strAnswer
	 * @return
	 * @throws SQLiteException
	 */
	public long updateAnswer(int nAnswerID, String strAnswer) throws SQLiteException
	{
		ContentValues values = new ContentValues();
		
		values.put(AnswerTBL.ANSWER, strAnswer);			// Answer
		
		String strWhere = AnswerTBL._ID + "=" + nAnswerID;
		
		SQLiteDatabase db = this.getM_DB();
		
		return db.update(PrayerDBInfo.ANSWER_TABLE_NAME, values, strWhere, null);
	}
	
	
	
	
	/*
	 * *****************************************************************************************************************************************
	 */
	public void debug_allTableView()
	{
		debug_categoryTBL();
		debug_prayerTBL();
		debug_answerTBL();
	}
	
	public void debug_categoryTBL()
	{
		String strQuery = "SELECT _id, catename FROM prayer_category_tbl ORDER BY _id ASC";
		
		SQLiteDatabase db = this.getM_DB();
		Cursor c = db.rawQuery(strQuery, null);
		c.moveToFirst();
		
		int nTotalCnt = c.getCount();
		
		Log.i( "prayer", "[[ Category ]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]" );
		
		for (int i = 0; i < nTotalCnt; i++)
		{
			Log.i( "prayer", "*************************************" );
			Log.i( "prayer", "id : " + c.getInt(0) );
			Log.i( "prayer", "cate name : " + c.getString(1) );
			
			c.moveToNext();
		}
		
		c.close();
	}
	
	public void debug_prayerTBL()
	{
		String strQuery = "SELECT _id, categoryid, prayer FROM prayer_tbl ORDER BY _id ASC";
		
		SQLiteDatabase db = this.getM_DB();
		Cursor c = db.rawQuery(strQuery, null);
		c.moveToFirst();
		
		int nTotalCnt = c.getCount();
		
		Log.i( "prayer", "[[ Prayer ]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]" );
		
		for (int i = 0; i < nTotalCnt; i++)
		{
			Log.i( "prayer", "*************************************" );
			Log.i( "prayer", "id : " + c.getInt(0) );
			Log.i( "prayer", "cate id : " + c.getInt(1) );
			Log.i( "prayer", "prayer : " + c.getString(2) );
			
			c.moveToNext();
		}
		
		c.close();
	}
	
	public void debug_answerTBL()
	{
		String strQuery = "SELECT _id, prayerid, answer FROM answer_tbl ORDER BY _id ASC";
		
		SQLiteDatabase db = this.getM_DB();
		Cursor c = db.rawQuery(strQuery, null);
		c.moveToFirst();
		
		int nTotalCnt = c.getCount();
		
		Log.i( "prayer", "[[ Answer ]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]" );
		
		for (int i = 0; i < nTotalCnt; i++)
		{
			Log.i( "prayer", "*************************************" );
			Log.i( "prayer", "id : " + c.getInt(0) );
			Log.i( "prayer", "prayer id : " + c.getInt(1) );
			Log.i( "prayer", "answer : " + c.getString(2) );
			
			c.moveToNext();
		}
		
		c.close();
	}
	
}
